package com.matt.repository.impl;

import com.matt.foundation.vo.children.BabyNClassInfoVO;
import com.matt.foundation.vo.classes.ClassesVo;
import com.matt.repository.base.BaseNativeSqlRepository;
import com.matt.repository.base.ClassNativeRepository;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;

/**
 * *******************************
 * Created by lch.
 * form com.matt
 * on 2018/11/22
 * *******************************
 */
@Service
public class ClassNativeRepositoryImpl extends BaseNativeSqlRepository implements ClassNativeRepository {
    @Override
    public List<ClassesVo> findByAreaIdAndCategoryAndDeleted(String areaId, Integer category) {
        String querySql = String.format("select cl.id as id,cl.created as created,cl.deleted as deleted,cl.name as name,cl.limitNum as limitNum," +
                " cl.beginMonth as beginMonth,cl.endMonth as endMonth,cl.targetObjectId as targetObjectId,cl.areaId as areaId,cl.category as category," +
                " cl.remark as remark,cl.inChargeTeacherName as inChargeTeacherName,cl.inChargeTeacherId as inChargeTeacherId,cl.baseTime as baseTime," +
                " (select count(1) from rel_classes_children as rcc inner join children as ch on rcc.childrenId=ch.id where rcc.classesId=cl.id and ch.status=1) as childrenNum  " +
                " from classes as cl where true and cl.areaId='%s' and deleted='%s'",areaId,false);

        if (category != null){
            querySql += String.format(" and category = '%s'", category);
        }

        return sqlObjectList(querySql,ClassesVo.class);
    }

    @Override
    public List<BabyNClassInfoVO> findByAreaIdAndCategoryAndChildrenIds(String areaId, Integer category,  String childName, String classId, List<String> childrenIds) {
        String querySql =
                "select c.id, " +
                "       c.name," +
                "       c.gender," +
                "       c.birthday," +
                "       c.imgUrl," +
                "       c.crmClienteleId, " +
                "       group_concat(cl.id) as classIds," +
                "       group_concat(cl.name) as classNames" +
                "  from children c" +
                "  left join rel_classes_children rcc on c.id = rcc.childrenId" +
                "  left join classes cl on rcc.classesId = cl.id" +
                " where c.aid = :1 and cl.category = :2" +
                (StringUtils.isNotBlank(classId) ? String.format(" and cl.id = \"%s\" ", classId) : "") +
                (StringUtils.isNotBlank(childName) ? String.format(" and c.name like \"%%%s%%\" ", childName) : "") +
                ((childrenIds!= null && childrenIds.size()>0) ? " and c.`crmClienteleId` in (:3) " : "") +
                " group by c.id" +
                " order by birthday desc" ;

        List param = new ArrayList();
        param.add(areaId);
        param.add(category);
        if ( (childrenIds!= null && childrenIds.size()>0) ) {
            param.add(childrenIds);
        }

        return sqlObjectList(querySql, param, BabyNClassInfoVO.class);
    }

    @Override
    public List<BabyNClassInfoVO> findByAreaIdAndCategoryAndChildrenIdsV2(String areaId, String childName, String classId, List<String> childrenIds) {
        String querySql =
                "select c.id, " +
                        "       c.name," +
                        "       c.gender," +
                        "       c.birthday," +
                        "       c.imgUrl," +
                        "       c.crmClienteleId, " +
                        "       group_concat(cl.id) as classIds," +
                        "       group_concat(cl.name) as classNames" +
                        "  from children c" +
                        "  left join rel_classes_children rcc on c.id = rcc.childrenId" +
                        "  left join classes cl on rcc.classesId = cl.id" +
                        " where c.aid = :1  " +
                        (StringUtils.isNotBlank(classId) ? String.format(" and cl.id = \"%s\" ", classId) : "") +
                        (StringUtils.isNotBlank(childName) ? String.format(" and c.name like \"%%%s%%\" ", childName) : "") +
                        ((childrenIds!= null && childrenIds.size()>0) ? " and c.`crmClienteleId` in (:2) " : "") +
                        " group by c.id" +
                        " order by birthday desc" ;

        List param = new ArrayList();
        param.add(areaId);
        if ( (childrenIds!= null && childrenIds.size()>0) ) {
            param.add(childrenIds);
        }

        return sqlObjectList(querySql, param, BabyNClassInfoVO.class);
    }

    @Override
    public List<BabyNClassInfoVO> findByAreaIdAndCategoryAndChildrenIds(String areaId, String childName, String classId) {
        String querySql =
                "select c.id, " +
                        "       c.name," +
                        "       c.gender," +
                        "       c.birthday," +
                        "       c.imgUrl," +
                        "       c.crmClienteleId, " +
                        "       group_concat(cl.id) as classIds," +
                        "       group_concat(cl.name) as classNames," +
                        "       rcc.status as relStatus " +
                        "  from children c" +
                        "  left join rel_classes_children rcc on c.id = rcc.childrenId" +
                        "  left join classes cl on rcc.classesId = cl.id" +
                        " where c.aid = :1  " +
                        (StringUtils.isNotBlank(classId) ? String.format(" and cl.id = \"%s\" ", classId) : "") +
                        (StringUtils.isNotBlank(childName) ? String.format(" and c.name like \"%%%s%%\" ", childName) : "") +
                        " group by c.id" +
                        " order by birthday desc" ;

        List param = new ArrayList();
        param.add(areaId);
        return sqlObjectList(querySql, param, BabyNClassInfoVO.class);
    }

    @Override
    public List<ClassesVo> findByAreaId(String areaId) {
    	  String querySql = String.format("select cl.id as id,cl.created as created,cl.deleted as deleted,cl.name as name,cl.limitNum as limitNum," +
                  " cl.beginMonth as beginMonth,cl.endMonth as endMonth,cl.targetObjectId as targetObjectId,cl.areaId as areaId,cl.category as category, " +
                  " cl.remark as remark,cl.inChargeTeacherName as inChargeTeacherName,cl.inChargeTeacherId as inChargeTeacherId,cl.baseTime as baseTime  " +
                  " from classes as cl where true and cl.areaId='%s' and deleted='%s'",areaId,false);
          return sqlObjectList(querySql,ClassesVo.class);
    }

    @Override
    public Long findCountByClassesId(String classesId) {
        String querySql = String.format("select count(1) from rel_classes_children as rcc inner join children as ch on rcc.childrenId=ch.id where ch.status=1 and rcc.classesId='%s' ",classesId);
        List list = sqlSingleList(querySql);
        if (list.size() > 0)
            return list.get(0)==null ? new Long(0):Long.parseLong(list.get(0).toString());
        return new Long(0);
    }

    @Override
    public Integer getTeacherChildCheckInCount(String staffId, String date) {
        String findSql = String.format("SELECT count(DISTINCT(cr.childrenId)) " +
                " FROM check_record as cr INNER JOIN children as ch ON ch.id=cr.childrenId " +
                " where date(cr.checkInTime)=date('%s') and (cr.status=1 or cr.status=2)" +
                " and ch.id IN (SELECT DISTINCT(rcc.childrenId) from rel_classes_teacher as rct INNER JOIN rel_classes_children as rcc on rct.classesId=rcc.classesId where rct.staffId='%s')",date,staffId);
        List list = sqlSingleList(findSql);
        return Integer.parseInt(list.get(0).toString());
    }

}
